<?php
/**
 * DBShop 电子商务系统
 *
 * ==========================================================================
 * @link      https://www.dbshop.net/
 * @copyright 北京珑大钜商科技有限公司，并保留所有权利。
 * @license   https://www.dbshop.net/license.html License
 * ==========================================================================
 *
 * @author    静静的风 <baron@loongdom.cn>
 *
 */

namespace Sales\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use Sales\Entity\Order;
use Sales\Entity\OrderGoods;
use Sales\Entity\OrderGoodsReturn;
use Sales\Repository\QuerySearch\OrderGoodsReturnSearch;

class OrderGoodsReturnRepository extends EntityRepository
{
    /**
     * 后台售后申请列表（退换货）
     * @param array $search
     * @return \Doctrine\ORM\Query
     */
    public function findOrderReturnList(array $search = [])
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('r', 'g.goodsName', 'g.goodsId', 'g.goodsImage', 'g.inSpecGoodsStr', 'o.currencySymbol')
            ->from(OrderGoodsReturn::class, 'r')
            ->innerJoin(OrderGoods::class, 'g', Join::WITH, 'r.orderGoodsId = g.orderGoodsId')
            ->innerJoin(Order::class, 'o', Join::WITH, 'r.orderId = o.orderId')
            ->orderBy('r.addTime', 'DESC');

        $query = OrderGoodsReturnSearch::querySearchData($search, $query);

        return $query->getQuery();
    }

    /**
     * 退换货统计
     * @param string $startTime
     * @param string $endTime
     * @return int|mixed|string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findOrderReturnCount($startTime = '', $endTime = '')
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('COUNT(r.returnId)')->from(OrderGoodsReturn::class, 'r');
        if (!empty($startTime)) $query->andWhere($query->expr()->gte('r.addTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('r.addTime', ':endTime'))->setParameter('endTime', $endTime);

        return $query->getQuery()->getSingleScalarResult();
    }

    /**
     * 订单商品退款金额
     * @param $startTime
     * @param $endTime
     * @return string
     * @throws \Doctrine\ORM\NoResultException
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findOrderReturnTotal($startTime = '', $endTime = '')
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('SUM(r.returnAmount)')->from(OrderGoodsReturn::class, 'r')
            ->where('r.returnState = :returnState')->setParameter('returnState', 2)
            ->andWhere($query->expr()->in('r.returnType', [1, 2]));

        if (!empty($startTime)) $query->andWhere($query->expr()->gte('r.reviewTime', ':startTime'))->setParameter('startTime', $startTime);
        if (!empty($endTime))   $query->andWhere($query->expr()->lte('r.reviewTime', ':endTime'))->setParameter('endTime', $endTime);

        return number_format($query->getQuery()->getSingleScalarResult(), 2, '.', '');
    }

    /**
     * 根据时间分组统计订单商品退货金额
     * @param $startTime
     * @param $endTime
     * @param $userGroupId
     * @param $dateType
     * @return array|false|mixed[]
     * @throws \Doctrine\DBAL\Driver\Exception
     * @throws \Doctrine\DBAL\Exception
     */
    public function findOrderReturnDateTotal($startTime, $endTime, $userGroupId = 0, $dateType = '%Y-%m-%d')
    {
        $query  = $this->getEntityManager()->getConnection();
        $sql = "SELECT SUM(r.return_amount) AS returnTotal,";

        $sql    .= " FROM_UNIXTIME(r.review_time, '".$dateType."') AS userDate";
        $sql    .= " FROM dbshop_order_goods_return AS r LEFT JOIN dbshop_user AS u ON u.user_id = r.user_id";

        $sql    .= " WHERE r.review_time >= ".$startTime;
        $sql    .= " AND r.review_time <= ".$endTime;
        $sql    .= " AND r.return_state = 2";
        $sql    .= " AND (r.return_type = 1 OR r.return_type = 2)";

        if ($userGroupId > 0) $sql .= ' AND u.user_group_id = '.$userGroupId;
        $sql    .= " GROUP BY userDate";

        $stmt   = $query->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }
    /*=======================================下面为前台使用=======================================*/
    /**
     * 前台客户的退换货信息
     * @param $userId
     * @return \Doctrine\ORM\Query
     */
    public function shopFindOrderReturnList($userId)
    {
        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('r', 'g.goodsName', 'g.goodsImage', 'g.inSpecGoodsStr')
            ->from(OrderGoodsReturn::class, 'r')
            ->innerJoin(OrderGoods::class, 'g', Join::WITH, 'r.orderGoodsId = g.orderGoodsId')
            ->where('r.userId = :userId')->setParameter('userId', $userId)
            ->orderBy('r.addTime', 'DESC');

        return $query->getQuery();
    }
}